package cc.webmania.android.akeletbolcsessege;

import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Calendar;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {
	//http://www.screaming-penguin.com/node/7742
	//http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/
	public static String DATABASE_NAME = "akeletbolcsessege.db";
	public static int DATABASE_VERSION = 8;
	private static final String TABLE_BOLCSESSEG = "bolcsessegek";
	public static String DATABASE_PATH = "/data/data/cc.webmania.android.akeletbolcsessege/databases/";

	private Context context;
	private static SQLiteDatabase db;
	private int upgradeFromVersion;
	private String savedFavorites;

	public DBHelper(Context context) throws Exception {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		//Log.d("aKeletB", "DBHelper constructor: " + DATABASE_NAME + " v" + DATABASE_VERSION);
		this.context = context;
		// avoid multiple DB connections
		if(db != null)
			return;
		boolean copy = false;
		// open existing DB or create new
		try {
			db = getWritableDatabase();
		} catch (Exception e) {
			copy = true;
		}
		// check if upgrade
		if(upgradeFromVersion > 0)
			copy = true;
		// check if new
		else if(db != null)
			try {
				db.query(TABLE_BOLCSESSEG, new String[]{"id"}, null, null, null, null, null);
			} catch (Exception e) {
				copy = true;
			}
		// close for copy
		if(copy && db != null)
			db.close();
		// copy and reopen
		if(copy)
		{
			copyDataBase();
			try {
				db = getWritableDatabase();
				restoreFavorites();
			} catch (RuntimeException e) {
				throw new Exception(context.getString(R.string.database_error_open), e);
			}
		}
	}


	@Override
	public void onCreate(SQLiteDatabase db) {
		//ide jönnek a telepítéskor szükséges parancsok
	}

	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//adatbázis upgradenél fut le 
		//Log.d("aKeletB", "onUpgrade()");
		upgradeFromVersion = oldVersion;
		// save favorites
		savedFavorites = "";
		try
		{
			StringBuilder sb = new StringBuilder();
			String sql = "SELECT id FROM bolcsessegek WHERE kedvenc = 1";
			Cursor cursor = db.rawQuery(sql, null);
			while(cursor.moveToNext())
				sb.append(cursor.getInt(0)).append(',');
			if(sb.length()>0)
				sb.setLength(sb.length()-1);
			savedFavorites = sb.toString();
			cursor.close();
		}
		catch(Exception e)
		{
		}

	}
	
	@Override
	public synchronized void close() {	 
        if(db != null)
    	    db.close();
        super.close();
	}
	
	/**
	 * Copies your database from your local assets-folder to the just created empty database in the
	 * system folder, from where it can be accessed and handled.
	 * This is done by transfering bytestream.
	 * */
	private void copyDataBase() throws Exception{
		//Log.d("aKeletB", "207 - copyDataBase");
		//Open your local db as the input stream
		InputStream myInput = context.getAssets().open(DATABASE_NAME);
		try {
			// Path to the just created empty db
			String outFileName = DATABASE_PATH + DATABASE_NAME;
	
			//Open the empty db as the output stream
			OutputStream myOutput = new FileOutputStream(outFileName);
	
			//transfer bytes from the inputfile to the outputfile
			byte[] buffer = new byte[1024];
			int length;
			while ((length = myInput.read(buffer))>0){
				myOutput.write(buffer, 0, length);
			}
	
			//Close the streams
			myOutput.flush();
			myOutput.close();
			myInput.close();
			//Log.d("aKeletB", "228 - copyDataBase befejeződött a DB felmásolva");
		} catch (Exception e) {
			//Log.d("aKeletB", "230 - copyDataBase exception");
			throw new Exception(context.getString(R.string.database_error_create), e);
		}
	}

	
	private void restoreFavorites() {
		if(savedFavorites == null || savedFavorites.length() == 0)
			return;
		try
		{
			String sql = "UPDATE bolcsessegek SET kedvenc=1  WHERE id IN (" + savedFavorites + ")";
			//Log.d("aKeletB", sql);
			db.execSQL(sql);
		}
		catch(SQLException e)
		{
		}
	}


	public Bolcsesseg getBolcsesseg(int bolcsessegId){
		//Log.d("aKeletB", "50 - getBolcsesseg()");
		Bolcsesseg bolcsesseg;
		bolcsesseg = new Bolcsesseg();
		try {
			//hány rekordunk van
			String sql = "SELECT COUNT(*) FROM " + TABLE_BOLCSESSEG;
			int count = 0;
			String[] selectionArgs = new String[]{};
			//Log.d("aKeletB", "58 - DB verzió: " + db.getVersion());
			
			Cursor cursor = db.rawQuery(sql, selectionArgs);
			if (cursor.moveToFirst()) {
				do {
					count = cursor.getInt(0);
				} while (cursor.moveToNext());
			}
			
			//válasszunk ki egy sort a count közül 366 nap
			//=napSorszam-INT(napSorszam/count)*count ha ez 0 => count
			Calendar cal = Calendar.getInstance();
			int doy = cal.get(Calendar.DAY_OF_YEAR);
			int id = (bolcsessegId > 0) ? bolcsessegId : (int) doy - (int) (doy/count) * count;		//ha kaptunk bolcsessegId-t akkor legyen az, ha nem akkor a mai napra való
			id = (id != 0) ? id : count;
			//id = new Random().nextInt(count-1)+1; véletlen választás
			//Log.d("aKeletB", "73 - id: " + id);
			sql = "SELECT bolcsessegek.bolcsesseg, forrasok.forras, forrasok.link, bolcsessegek.kedvenc, bolcsessegek.id " +
					" FROM bolcsessegek, forrasok" +
					" WHERE bolcsessegek.forras_id = forrasok.id" +
					" AND bolcsessegek.id = " + id;
			
			cursor = db.rawQuery(sql, selectionArgs);
			if (cursor.moveToFirst()) {
				do {
					//Log.d("aKeletB", "82 - bölcsesség link: " + cursor.getString(2));
					bolcsesseg.setBolcsesseg(cursor.getString(0));
					bolcsesseg.setForras(cursor.getString(1));
					bolcsesseg.setLink(cursor.getString(2));
					bolcsesseg.setKedvenc(cursor.getInt(3));
					bolcsesseg.setId(cursor.getInt(4));
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			//Log.d("aKeletB", "93 - getBolcsesseg Exception");
			e.printStackTrace();
		}		
		return bolcsesseg;
	}
	
	public void changeKedvenc(int id, int kedvenc){
		//kedvenc be vagy kijelölése
		try {
			String sql = "UPDATE bolcsessegek SET kedvenc = " + kedvenc + " WHERE id = " + id;
			//Log.d("aKeletB", sql);
			//RLog.d(sql);
			db.execSQL(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public ArrayList<Bolcsesseg> getKedvencBolcsessegek() {
		ArrayList<Bolcsesseg> kedvencBolcsessegek = new ArrayList<Bolcsesseg>();
		
		String sql = "SELECT bolcsessegek.id, bolcsessegek.bolcsesseg, forrasok.forras " +
						" FROM bolcsessegek, forrasok " +
						" WHERE bolcsessegek.forras_id = forrasok.id" +
						" AND bolcsessegek.kedvenc = 1";
		
		String[] selectionArgs = new String[]{};
		
		Cursor cursor = db.rawQuery(sql, selectionArgs);
		if (cursor.moveToFirst()) {
			do{
				Bolcsesseg bolcsesseg = new Bolcsesseg();
				bolcsesseg.setId(cursor.getInt(0));
				bolcsesseg.setBolcsesseg(cursor.getString(1));
				bolcsesseg.setForras(" - " + cursor.getString(2) + " - ");
				kedvencBolcsessegek.add(bolcsesseg);
				
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return kedvencBolcsessegek;
	}
}
